﻿Public Class ClsTVoucherMaxNumberDbAccess
    Inherits ClsBaseDbAccess

    '对应表：T_VoucherNumber - 三个字段，多行数据，WorkPeriod(会计期间)、GroupShortName(类别字号)、MaxNumber(该类别，当前期间最大凭证号)
    '        当下只处理T_VoucherNumber表，

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_VOUCHER_NUMBER)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    ''' <summary>
    ''' 返回指定期间的全部类别的最大凭证号
    ''' </summary>
    ''' <param name="p"></param>
    ''' <returns></returns>
    Public Function GetMaxNubmerByPeiod(ByVal p As String) As List(Of ClsTVoucherNumber)
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpParas(0) As OleDb.OleDbParameter
            Dim tmpRet As List(Of ClsTVoucherNumber)
            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Select * from [" & Me.AppTableName & "] "
            tmpSql &= " Where [WorkPeriod] = @WorkPeriod "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            'tmpParas(1) = New OleDb.OleDbParameter("@GroupShortName", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = p
            'tmpParas(1).Value = s

            '查询并将返回数据解析为List对象
            tmpRet = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTVoucherNumber))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpRet

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherMaxNumberDbAccess - GetMaxNumberByPeiod"

            Return Nothing
        End Try
    End Function

    ''' <summary>
    ''' 返回指定期间和类别 的最大凭证号
    ''' </summary>
    ''' <param name="p"></param>
    ''' <param name="s"></param>
    ''' <returns></returns>
    Public Function GetMaxNumberByPeiodAndShortName(ByVal p As String, ByVal s As String) As ClsTVoucherNumber
        Dim tmpRet As ClsTVoucherNumber = Nothing

        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpParas(1) As OleDb.OleDbParameter
            Dim tmpList As List(Of ClsTVoucherNumber)

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Select * from [" & Me.AppTableName & "] "
            tmpSql &= " Where [WorkPeriod] = @WorkPeriod and [GroupShortName] = @GroupShortName "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@GroupShortName", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = p
            tmpParas(1).Value = s

            '查询并将返回数据解析为List对象
            tmpList = TryCast(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTVoucherNumber))
            If tmpList IsNot Nothing AndAlso tmpList.Count > 0 Then
                tmpRet = tmpList.Item(0)
            End If


            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherMaxNumberDbAccess - GetMaxNumberByPeiodAndShortName"

        End Try

        Return tmpRet

    End Function

    Public Function UpdateMaxNumber(ByVal p As String, ByVal g As String, ByVal maxN As Integer) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpParas(2) As OleDb.OleDbParameter
            Dim tmpReturn As Int32

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] "
            tmpSql &= " Set [MaxNumber]= @MaxNumber "
            tmpSql &= " Where [WorkPeriod] = @WorkPeriod and [GroupShortName]=@GroupShortName "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@MaxNumber", OleDb.OleDbType.Integer)
            tmpParas(1) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@GroupShortName", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = maxN
            tmpParas(1).Value = p
            tmpParas(2).Value = g

            '保存
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherMaxNumberDbAccess - SaveMaxNumber"

            Return Nothing
        End Try
    End Function

    Public Function InsertMaxNumber(ByVal tmpCls As ClsTVoucherNumber) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()
            Dim tmpInsertParas(2) As OleDb.OleDbParameter
            Dim tmpReturn As Int32

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Insert into [" & Me.AppTableName & "]"
            tmpSql &= " ([WorkPeriod],[GroupShortName],[MaxNumber]) values("
            tmpSql &= "@WorkPeriod,@GroupShortName,@MaxNumber"
            tmpSql &= ")"

            '构造参数集合
            tmpInsertParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpInsertParas(1) = New OleDb.OleDbParameter("@GroupShortName", OleDb.OleDbType.VarWChar)
            tmpInsertParas(2) = New OleDb.OleDbParameter("@MaxNumber", OleDb.OleDbType.Integer)
            '参数赋值
            tmpInsertParas(0).Value = IIf(String.IsNullOrEmpty(tmpCls.WorkPeriod), "", tmpCls.WorkPeriod)
            tmpInsertParas(1).Value = tmpCls.GroupShortName
            tmpInsertParas(2).Value = tmpCls.MaxNumber

            '插入并返回自动编号
            tmpReturn = tmpDbAccess.InsertData(tmpSql, tmpInsertParas, True)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherMaxNumberDbAccess - InsertMaxNumber"

            Return Nothing
        End Try

    End Function
    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object

        If reader Is Nothing Then
            Return Nothing
        End If

        Dim tmpClsList As List(Of ClsTVoucherNumber) = New List(Of ClsTVoucherNumber)

        While reader.Read()

            Dim tmpCls As ClsTVoucherNumber = New ClsTVoucherNumber()

            With tmpCls
                .VoucherNumberId = reader.GetInt32(0)
                .WorkPeriod = reader.GetString(1)
                .GroupShortName = reader.GetString(2)
                .MaxNumber = reader.GetInt32(3)
            End With

            tmpClsList.Add(tmpCls)

        End While


        Return tmpClsList

    End Function

End Class
